/**
 * 
 */
package com.gfwy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.gfwy.dao.IMsgDao;
import com.gfwy.vo.MsgBox;

/**
 * @author Administrator
 *
 */
public class MsgDaoImpl implements IMsgDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	public MsgDaoImpl(Connection conn) {
		this.conn = conn;
	}
	/* (non-Javadoc)
	 * @see com.hospital.dao.IMsgDao#findAll(java.lang.String, int, int)
	 */
	@Override
	public List<MsgBox> findAll(String keyWord, int startIndex, int count, boolean all)
			throws Exception {
		List<MsgBox> list = null;
		keyWord = (keyWord == null) ? "" : keyWord;
		try{
			//String sql = "SELECT * FROM messagebox WHERE title LIKE ? OR content LIKE ? ORDER BY pubdate DESC LIMIT ? , ?";
			String sql = "SELECT * FROM messagebox WHERE content LIKE ? ";
			if(all == false) {
				sql += "AND active=? ORDER BY pubdate DESC LIMIT ? , ?";
				this.pstmt = this.conn.prepareStatement(sql);
				this.pstmt.setString(1, "%" + keyWord + "%");
				this.pstmt.setBoolean(2, true);
				this.pstmt.setInt(3, startIndex);
				this.pstmt.setInt(4, count);
			} else {
				sql += "ORDER BY pubdate DESC LIMIT ? , ?";
				this.pstmt = this.conn.prepareStatement(sql);
				this.pstmt.setString(1, "%" + keyWord + "%");
				this.pstmt.setInt(2, startIndex);
				this.pstmt.setInt(3, count);
			}
			
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<MsgBox>();
			while(this.rs.next()){
				MsgBox msgBox = new MsgBox();
				msgBox.setMid(this.rs.getInt("mid"));
				msgBox.setNickname(this.rs.getString("nickname"));
				msgBox.setEmail(this.rs.getString("email"));
				msgBox.setHomepage(this.rs.getString("homepage"));
				msgBox.setQq(this.rs.getString("qq"));
				msgBox.setFrom(this.rs.getString("from"));
				msgBox.setOpen(this.rs.getBoolean("open"));
				msgBox.setTitle(this.rs.getString("title"));
				msgBox.setContent(this.rs.getString("content"));
				msgBox.setActive(this.rs.getBoolean("active"));
				msgBox.setPubdate(this.rs.getTimestamp("pubdate"));
				msgBox.setReply(this.rs.getString("reply"));
				list.add(msgBox);
			}
		}catch(Exception e){
			throw e;
		}finally{
			if(this.rs != null){
				try{
					this.rs.close();
				}catch(Exception e){
					throw e;
				}
			}
			if(this.pstmt != null){
				try{
					this.pstmt.close();
				}catch(Exception e){
					throw e;
				}
			}
		}
		return list;
	}

	/* (non-Javadoc)
	 * @see com.hospital.dao.IMsgDao#count()
	 */
	@Override
	public int count(String keyWord, boolean all) throws Exception {
		int count = 0;
		keyWord = (keyWord == null) ? "" : keyWord;
		try {
			String sql = "SELECT COUNT(*) AS count FROM messagebox WHERE content LIKE ? ";
			if(all == false) {
				sql += "AND active=?";
				this.pstmt = this.conn.prepareStatement(sql);
				this.pstmt.setString(1, "%" + keyWord + "%");
				this.pstmt.setBoolean(2, true);
			} else {
				this.pstmt = this.conn.prepareStatement(sql);
				this.pstmt.setString(1, "%" + keyWord + "%");
			}
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()){
				count = this.rs.getInt("count");
			}
		} catch (Exception e) {
			throw e;
		} finally{
			if(this.rs != null){
				try{
					this.rs.close();
				}catch(Exception e){
					throw e;
				}
			}
			if(this.pstmt != null){
				try{
					this.pstmt.close();
				}catch(Exception e){
					throw e;
				}
			}
		}
		return count;
	}
	@Override
	public boolean doDel(int mid) throws Exception {
		boolean flag = false;
		try {
			String sql = "DELETE FROM messagebox WHERE mid = ?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, mid);
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch(Exception e){
					throw e;
				}
			}
		}
		return flag;
	}
	@Override
	public boolean doUpdate(int mid, boolean active) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE messagebox SET active=? WHERE mid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setBoolean(1, active);
			this.pstmt.setInt(2, mid);
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		}
		return flag;
	}
	@Override
	public boolean doCreate(MsgBox msg) throws Exception {
		boolean flag = false;
		try {
			String sql = "INSERT INTO messagebox(content,pubdate,active) VALUES (?,?,?)";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, msg.getContent());
			this.pstmt.setTimestamp(2, new java.sql.Timestamp(msg.getPubdate().getTime()));
			this.pstmt.setBoolean(3, msg.isActive());
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}
	@Override
	public boolean doReply(String reply, int mid) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE messagebox SET reply=? WHERE mid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, reply);
			this.pstmt.setInt(2, mid);
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

}
